﻿using FinancialReportPowerPointAddIn.Helper;
using Microsoft.Office.Interop.PowerPoint;
using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using System.Drawing;

namespace FinancialReportPowerPointAddIn.PPTModule
{
    public static class GenerateFinancialOverview
    {
        public static void Write(int year, int month, int comYear, int comMonth)
        {
            //获取当前ppt中所有的幻灯片
            var slides = Globals.ThisAddIn.Application.ActivePresentation.Slides;
            //在当前选中幻灯片前插入新的幻灯片--代码提示中没有F12进去才找到的方法
            Slide currentslide = slides.Add(slides.Count + 1, PpSlideLayout.ppLayoutBlank);
            currentslide.Select();
            //写入标题           
            Shape titleBox = currentslide.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 0, 0, 370, 15);
            titleBox.TextFrame.TextRange.Text = $"诚福财务概况{year}{month.ToString("00")}";
            titleBox.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoCTrue;
            //写入表格            
            int colsCount = (year - 1) == comYear ? 19 : 21;
            Shape tableShape = currentslide.Shapes.AddTable(10, colsCount, 0, 30, currentslide.Master.Width - 100, currentslide.Master.Height - 50);
            Table table = tableShape.Table;
            // 编写标题行
            //写入MTD  要调转颜色RGB =>BGR          
            table.Cell(1, 1).Shape.TextFrame.TextRange.Text = $"{month}月";
            table.Cell(1, 1).Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoCTrue;
            table.Cell(1, 1).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, 1).Shape.Fill.ForeColor.RGB = Color.FromArgb(255, 255, 255).ToArgb();
            table.Cell(1, 1).Merge(tableShape.Table.Cell(2, 1));           
            BorderHelper.SetRightBorder(table.Cell(1,1));
            //开始分情况处理
            //写入营业额
            int currentCol = 2;
            table.Cell(1, currentCol).Shape.Fill.ForeColor.RGB = Color.FromArgb(144, 238, 144).ToArgb();
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Text = "营业额";
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
            if (colsCount == 21)
            {
                table.Cell(1, currentCol).Merge(tableShape.Table.Cell(1, currentCol + 1));
                table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = $"增长\r\nVs{comYear}";
                table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(144, 238, 144).ToArgb();
            }
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = $"增长\r\nVs{year - 1}";
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(144, 238, 144).ToArgb();
            //写入交易次数
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Text = "交易次数GC";
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
            table.Cell(1, currentCol).Shape.Fill.ForeColor.RGB = Color.FromArgb(135, 206, 250).ToArgb();
            if (colsCount == 21)
            {
                table.Cell(1, currentCol).Merge(tableShape.Table.Cell(1, currentCol + 1));
                table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = $"增长\r\nVs{comYear}";
                table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(135, 206, 250).ToArgb();                
            }
            BorderHelper.SetRightBorder(table.Cell(1, currentCol));
            BorderHelper.SetRightBorder(table.Cell(2, currentCol));
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = $"增长\r\nVs{year - 1}";
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(135, 206, 250).ToArgb();
            //写入营业额完成率
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Text = "营业额完成率";
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, currentCol).Shape.Fill.ForeColor.RGB = Color.FromArgb(193, 182, 255).ToArgb();
            table.Cell(1, currentCol).Merge(tableShape.Table.Cell(1, currentCol + 3));
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "目标";
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(193, 182, 255).ToArgb();
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "实际";
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(193, 182, 255).ToArgb();
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "目标\r\n差异%";
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(193, 182, 255).ToArgb();
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "同比\r\n绝对值\r\n差异";
            BorderHelper.SetRightBorder(table.Cell(1, currentCol));
            BorderHelper.SetRightBorder(table.Cell(2, currentCol));
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(193, 182, 255).ToArgb();
            //写入PAC
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Text = "PAC";
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, currentCol).Shape.Fill.ForeColor.RGB = Color.FromArgb(64, 224, 208).ToArgb();
            table.Cell(1, currentCol).Merge(tableShape.Table.Cell(1, currentCol + 3));
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "目标";
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(64, 224, 208).ToArgb();
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "实际";
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(64, 224, 208).ToArgb();
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "目标\r\n差异%";
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(64, 224, 208).ToArgb();
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "同比\r\n百分比\r\n差异";
            BorderHelper.SetRightBorder(table.Cell(1, currentCol));
            BorderHelper.SetRightBorder(table.Cell(2, currentCol));
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(64, 224, 208).ToArgb();
            //写入SOI
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Text = "SOI";
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
            table.Cell(1, currentCol).Shape.Fill.ForeColor.RGB = Color.FromArgb(190, 224, 58).ToArgb();
            table.Cell(1, currentCol).Merge(tableShape.Table.Cell(1, currentCol + 3));
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "目标";
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(190, 224, 58).ToArgb();
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "实际";
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(190, 224, 58).ToArgb();
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "目标\r\n绝对值\r\n差异";
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(190, 224, 58).ToArgb();
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "同比\r\n绝对值\r\n差异";
            BorderHelper.SetRightBorder(table.Cell(1, currentCol));
            BorderHelper.SetRightBorder(table.Cell(2, currentCol));
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(190, 224, 58).ToArgb();
            //写入现金流
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Text = "餐厅现金流";
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
            table.Cell(1, currentCol).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, currentCol).Shape.Fill.ForeColor.RGB = Color.FromArgb(189, 183, 107).ToArgb();
            table.Cell(1, currentCol).Merge(tableShape.Table.Cell(1, currentCol + 3));
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "目标";
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(189, 183, 107).ToArgb();
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "实际";
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(189, 183, 107).ToArgb();
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "目标\r\n差异%";
            table.Cell(2, currentCol++).Shape.Fill.ForeColor.RGB = Color.FromArgb(189, 183, 107).ToArgb();
            table.Cell(2, currentCol).Shape.TextFrame.TextRange.Text = "同比\r\n绝对值\r\n差异";
            table.Cell(2, currentCol).Shape.Fill.ForeColor.RGB = Color.FromArgb(189, 183, 107).ToArgb();
            //开始写入数据
            string[] restaurants = new string[] { "汇总", "潭江店", "开大店", "白石店", "西粤店", "东湖店", "恩平店", "油十店" };
            int currentrow = 3;
            using (var db = new FinancialReportContext())
            {
                foreach (string item in restaurants)
                {
                    currentCol = 1;
                    table.Cell(currentrow, currentCol).Shape.TextFrame.TextRange.Text = item == "汇总" ? "诚福公司" : item;
                    BorderHelper.SetRightBorder(table.Cell(currentrow, currentCol));                   
                    table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Font.Size = 14;
                    string flag = string.Empty;
                    string code = string.Empty;
                    Common.GetPLNameAndCode(item, out flag, out code);
                    //写入营业额增长
                    var cy = db.PLDatas.FirstOrDefault(x => x.Year == year && x.Month == month && x.IsPlan == false && x.Restaurant == flag);
                    var cyplan = db.PLDatas.FirstOrDefault(x => x.Year == year && x.Month == month && x.IsPlan == true && x.Restaurant == flag);
                    if (colsCount == 21)
                    {
                        var comy = db.PLDatas.FirstOrDefault(x => x.Year == comYear && x.Month == comMonth && x.IsPlan == false && x.Restaurant == flag);
                        var comp = (cy.营业净收入 / comy.营业净收入) - 1;
                        table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Text = comp.ToString("p");
                    }
                    var ly = db.PLDatas.FirstOrDefault(x => x.Year == year - 1 && x.Month == month && x.IsPlan == false && x.Restaurant == flag);
                    var lcomp = (cy.营业净收入 / ly.营业净收入) - 1;
                    table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Text = lcomp.ToString("p");
                    //写入GC增长 
                    if (item == "汇总")
                    {
                        var cysgc = db.PlatformSales.Where(x => x.Year == year && x.Month == month).Sum(x => x.全店GC);
                        if (colsCount == 21)
                        {
                            var comysgc = db.PlatformSales.Where(x => x.Year == comYear && x.Month == comMonth).Sum(x => x.全店GC);
                            var comp = (cysgc / comysgc) - 1;
                            table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Text = comp.ToString("p");
                        }
                        var lysgc = db.PlatformSales.Where(x => x.Year == year - 1 && x.Month == month).Sum(x => x.全店GC);
                        lcomp = (cysgc / lysgc) - 1;
                        table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Text = lcomp.ToString("p");

                    }
                    else
                    {
                        var cys = db.PlatformSales.FirstOrDefault(x => x.Year == year && x.Month == month && x.Uscode == code);
                        if (colsCount == 21)
                        {

                            var comys = db.PlatformSales.FirstOrDefault(x => x.Year == comYear && x.Month == comMonth && x.Uscode == code);
                            var comp = (cys.全店GC / comys.全店GC) - 1;
                            table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Text = comp.ToString("p");
                        }
                        var lcomys = db.PlatformSales.FirstOrDefault(x => x.Year == year - 1 && x.Month == month && x.Uscode == code);
                        lcomp = (cys.全店GC / lcomys.全店GC) - 1;                        
                        table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Text = lcomp.ToString("p");
                    }
                    BorderHelper.SetRightBorder(table.Cell(currentrow, currentCol-1));
                    //写入营业额完成率
                    table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Text = cyplan.营业净收入.ToString("#,##0");
                    table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Text = cy.营业净收入.ToString("#,##0");
                    Common.WriteVar(table, currentrow, currentCol++, Calculate.CalVar(cy.营业净收入, cyplan.营业净收入), "p");
                    BorderHelper.SetRightBorder(table.Cell(currentrow, currentCol));
                    Common.WriteVar(table, currentrow, currentCol++, cy.营业净收入 - ly.营业净收入, "#,##0");
                    //写入PAC
                    var cypac = cy.可控利润 / cy.产品净收入;
                    var cyplanpac = cyplan.可控利润 / cyplan.产品净收入;
                    var lypac = ly.可控利润 / ly.产品净收入;
                    table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Text = cyplanpac.ToString("p");
                    table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Text = cypac.ToString("p");
                    Common.WriteVar(table, currentrow, currentCol++, cypac - cyplanpac, "p");
                    BorderHelper.SetRightBorder(table.Cell(currentrow, currentCol));
                    Common.WriteVar(table, currentrow, currentCol++, cypac - lypac, "p");
                    //写入SOI
                    table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Text = cyplan.营运利润.ToString("#,##0");
                    table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Text = cy.营运利润.ToString("#,##0");
                    Common.WriteVar(table, currentrow, currentCol++, cy.营运利润 - cyplan.营运利润, "#,##0");
                    BorderHelper.SetRightBorder(table.Cell(currentrow, currentCol));
                    Common.WriteVar(table, currentrow, currentCol++, cy.营运利润 - ly.营运利润, "#,##0");
                    //写入现金流
                    table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Text = cyplan.餐厅现金流.ToString("#,##0");
                    table.Cell(currentrow, currentCol++).Shape.TextFrame.TextRange.Text = cy.餐厅现金流.ToString("#,##0");
                    Common.WriteVar(table, currentrow, currentCol++, Calculate.CalVar(cy.餐厅现金流, cyplan.餐厅现金流), "p");
                    Common.WriteVar(table, currentrow, currentCol, cy.餐厅现金流 - ly.餐厅现金流, "#,##0");
                    //
                    currentrow++;
                }
            }
            //结束写入数据
            //整理格式

            Common.OrganizeFormat(table, 14, 12);
            Common.AutoFit(table);
            tableShape.Height = currentslide.Master.Height - 50;
            tableShape.Width = currentslide.Master.Width;
        }        
    }
}
